package com.navi.loaddata.loadService.impl.DefectData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.DReloadPnlGls;
import com.navi.loaddata.core.gpEntity.MinMaxEvttimeStamp;
import com.navi.loaddata.core.gpEntity.YearMonth;
import com.navi.loaddata.core.gpEntity.base.ReloadPanel;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.mysqlEntity.EtlConfDDefect;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.util.JacksonUtil;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.loadService.SpringContext;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.postgresql.util.PSQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;

import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class LoadDefectDataBean {
    private static final Logger logger = LoggerFactory.getLogger(LoadDefectDataBean.class);

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Autowired
    private MySqlRepositoryImpl dao;

    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void deletePeriodData(EtlConfDModel etlConfDModel, String tmpTab, String defectShop, String EvtStartTime, String EvtEndTime) {
        Session session = gpDao.getEntityManagerSession();

        StringBuffer sqlDelDef = new StringBuffer();
        sqlDelDef.append("delete from " + tmpTab);

        //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
        if (etlConfDModel.getExt1().toString().equals("Y")) {
            sqlDelDef.append(" where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
            sqlDelDef.append(" and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
        } else {
//            sqlDelDef.append(" where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
//            sqlDelDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
//            sqlDelDef.append(" and evt_timestamp >= '" + EvtStartTime + "'");
//            sqlDelDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
            sqlDelDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
            sqlDelDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
        }
        sqlDelDef.append(" and ope_id = '"+ etlConfDModel.getOpe() +"'");

        try {
            SQLQuery DelDef_query = session.createSQLQuery(sqlDelDef.toString());
            logger.info("Delete Data SQL:" + sqlDelDef.toString());

            Date startTime = new Date();

            DelDef_query.executeUpdate();

            Date end = new Date();
            long durableTime = (end.getTime() - startTime.getTime())/1000;

            logger.info(defectShop + " "+  tmpTab +" delete from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
            logger.info("Delete Data耗时:" + durableTime +"s");
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlDelDef.toString():  **********    " + sqlDelDef.toString());
            e.printStackTrace();
            try {
                throw new ETLException2(ErrorCode.ERROR_DELETE_GP_DEFECT_SUM, e);
            } catch (ETLException2 e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }
        }
    }

    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void deleteDuplicateData(EtlConfDModel etlConfDModel, String tmpTab, String defectShop, String EvtStartTime, String EvtEndTime, String errMsg, int errCnt, Boolean loopFlag, String MaxEndTime, List<YearMonth> yearMonthList) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        String startTime = "";

        //获取panel的最小release时间，作为起始时间
        String nowTime = LocalDateTime.now().toString();
        int month = Integer.parseInt(yearMonthList.get(0).getMonth(), 16);
        if (month < 10 ) {
            startTime = nowTime.substring(0, 2) + yearMonthList.get(0).getYear() + "-0" + month +"-01 00:00:00";
        } else {
            startTime = nowTime.substring(0, 2) + yearMonthList.get(0).getYear() + "-" + month +"-01 00:00:00";
        }

        StringBuffer sqlsumDef = new StringBuffer();
        StringBuffer sqlbuffDel = new StringBuffer();

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("466X");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("6641");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
        }

        //一些站点数据只存glass表
        if (opeList.contains(etlConfDModel.getOpe())) {
            //获取源数据汇总defectqty
            sqlsumDef.append(" select distinct glass_id");
            sqlsumDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_glass_f");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlsumDef.append(" where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp < '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlsumDef.append(" and ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlsumDef.append(" and real_flg = 'Y'");
//            sqlsumDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlsumDef.append(" and panel_id in ('"+ panelList + "')");
            }


            //By ope_id、panel_id删除重复数据
            sqlbuffDel.append("delete from " + tmpTab + " a ");
            sqlbuffDel.append(" where panel_id in ");
            sqlbuffDel.append(" (" + sqlsumDef + ")");
            sqlbuffDel.append(" and a.evt_timestamp >= '" + startTime + "'");


            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + MaxEndTime + "'");
            } else {
                sqlbuffDel.append(" and a.evt_timestamp <= '" + EvtEndTime + "'");
            }
        }else if ("wpp_default_adefect_sum".equals(tmpTab) || "wpp_default_fdefect_sum".equals(tmpTab) || "wpp_default_cdefect_sum".equals(tmpTab)) {
            //获取源数据汇总defectqty
            sqlsumDef.append(" select distinct panel_id, ope_id");
            sqlsumDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlsumDef.append(" where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp < '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlsumDef.append(" and ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlsumDef.append(" and real_flg = 'Y'");
//            sqlsumDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlsumDef.append(" and panel_id in ('"+ panelList + "')");
            }

            //By ope_id、panel_id删除重复数据
            sqlbuffDel.append("delete from " + tmpTab + " a ");
            sqlbuffDel.append(" where (panel_id,ope_id) in ");
            sqlbuffDel.append(" (" + sqlsumDef + ")");
            sqlbuffDel.append(" and a.evt_timestamp >= '" + startTime + "'");


            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + MaxEndTime + "'");
            } else {
                sqlbuffDel.append(" and a.evt_timestamp <= '" + EvtEndTime + "'");
            }
        } else {
            //获取源数据汇总defectqty
            sqlsumDef.append(" select distinct panel_id");
            sqlsumDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlsumDef.append(" where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp < '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlsumDef.append(" and ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlsumDef.append(" and real_flg = 'Y'");
//            sqlsumDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlsumDef.append(" and panel_id in ('"+ panelList + "')");
            }

            //By ope_id、panel_id删除重复数据
            sqlbuffDel.append("delete from " + tmpTab + " a ");
            sqlbuffDel.append(" where panel_id in ");
            sqlbuffDel.append(" (" + sqlsumDef + ")");
            sqlbuffDel.append(" and a.evt_timestamp >= '" + startTime + "'");

            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + MaxEndTime + "'");
            } else {
                sqlbuffDel.append(" and a.evt_timestamp <= '" + EvtEndTime + "'");
            }
        }

        try {
            SQLQuery DeltDef_query = session.createSQLQuery(sqlbuffDel.toString());
            logger.info("Delete duplicate data SQL:" + sqlbuffDel.toString());

            Date startDate = new Date();

            DeltDef_query.executeUpdate();

            Date end = new Date();
            long durableTime = (end.getTime() - startDate.getTime())/1000;
            logger.info(defectShop + " "+  tmpTab +" delete duplicate data from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
            logger.info("Delete duplicate data耗时:" + durableTime +"s");
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlbuffDel.toString():  **********    " + sqlbuffDel.toString());

            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }

            errCnt++;

            logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
            e.printStackTrace();

            throw new RuntimeException(e);
        }
    }

    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void insertData(EtlConfDModel etlConfDModel, String tmpTab, String defectShop, String EvtStartTime, String EvtEndTime, String errMsg, int okCnt, Boolean loopFlag) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        StringBuffer sqlBuffDef = new StringBuffer();
        StringBuffer sqlInsrDef = new StringBuffer();

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
        }

        //一些站点数据只存glass表
        if (opeList.contains(etlConfDModel.getOpe())) {
            //获取源数据汇总defectqty
            sqlBuffDef.append(" select panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id, grade_id, tft_glass_id, cf_glass_id, defect_flg,");
            sqlBuffDef.append(" operator_id, rep_res, lcvd_rep_time, pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty from (");
            sqlBuffDef.append(" select defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, coalesce(defectHis.defect_code, '-') as defect_code, coalesce(defectHis.defect_size,'E') as defect_size, now() as db_timestamp, defectHis.cald_date,");
            sqlBuffDef.append(" defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner as owner_id,defectHis.pnl_grade as grade_id,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.panel_id, defectHis.ope_id, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.defect_flg, defectHis.rep_res order by defectHis.evt_timestamp desc) as rowCnt,");
            sqlBuffDef.append(" defectHis.ext_1 as tft_glass_id, '' cf_glass_id, coalesce(defectHis.defect_flg,'-') as defect_flg, defectHis.operator_id, coalesce(defectHis.rep_res,'-') as rep_res, sum(defectHis.lcvd_rep_time) as lcvd_rep_time, '-' as pnl_rej_type,");
            sqlBuffDef.append(" '-' as pnl_judgment_grade, '-' as pnl_jge, 0 as pnl_defect_cnt, '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as defectqty");
//            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis");
            sqlBuffDef.append(" left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_glass_f panel");
            sqlBuffDef.append(" on defectHis.panel_id = panel.glass_id");
            sqlBuffDef.append(" and defectHis.evt_timestamp = panel.evt_timestamp");
            sqlBuffDef.append(" and defectHis.ope_id = panel.ope_id");
            sqlBuffDef.append(" and defectHis.real_flg = panel.real_flg");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and panel.panel_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.cald_date, defectHis.shf_key_fk, defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.lot_id,");
            sqlBuffDef.append(" defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, defectHis.pnl_grade, defectHis.ext_1, defectHis.defect_flg,");
            sqlBuffDef.append(" defectHis.operator_id, defectHis.rep_res");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date,");
            sqlInsrDef.append(" shf_key, shf_id, hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id,grade_id,");
            sqlInsrDef.append(" tft_glass_id, cf_glass_id, defect_flg, operator_id, rep_res, lcvd_rep_time, pnl_rej_type,");
            sqlInsrDef.append(" pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty)" + sqlBuffDef);
        } else if ("466X".equals(etlConfDModel.getOpe()) || "6641".equals(etlConfDModel.getOpe())) {
            //获取源数据汇总defectqty
            sqlBuffDef.append(" select panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id, grade_id, tft_glass_id, cf_glass_id, defect_flg,");
            sqlBuffDef.append(" operator_id, rep_res, lcvd_rep_time, pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_defect_cnt,");
            sqlBuffDef.append(" cel_repair_defect_code, cel_repair_eng_defect_code, cel_repair_jge_code, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty from (");
            sqlBuffDef.append(" select defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, coalesce(defectHis.defect_code, '-') as defect_code, coalesce(defectHis.defect_size,'E') as defect_size, now() as db_timestamp, defectHis.cald_date,");
            sqlBuffDef.append(" defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner as owner_id,defectHis.pnl_grade as grade_id,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.panel_id, defectHis.ope_id, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.defect_flg, defectHis.rep_res, defectHis.cel_repair_defect_code, defectHis.cel_repair_eng_defect_code, defectHis.cel_repair_jge_code order by defectHis.evt_timestamp desc) as rowCnt,");
            sqlBuffDef.append(" defectHis.ext_1 as tft_glass_id, '' cf_glass_id, coalesce(defectHis.defect_flg,'-') as defect_flg, coalesce(defectHis.cel_repair_defect_code,'-') as cel_repair_defect_code, coalesce(defectHis.cel_repair_eng_defect_code,'-') as cel_repair_eng_defect_code, ");
            sqlBuffDef.append(" coalesce(defectHis.cel_repair_jge_code,'-') as cel_repair_jge_code, defectHis.operator_id, coalesce(defectHis.rep_res,'-') as rep_res, sum(defectHis.lcvd_rep_time) as lcvd_rep_time, '-' as pnl_rej_type, '-' as pnl_judgment_grade, '-' as pnl_jge, 0 as pnl_defect_cnt,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as defectqty");
//            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis");
            sqlBuffDef.append(" left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_glass_f panel");
            sqlBuffDef.append(" on defectHis.panel_id = panel.glass_id");
            sqlBuffDef.append(" and defectHis.evt_timestamp = panel.evt_timestamp");
            sqlBuffDef.append(" and defectHis.ope_id = panel.ope_id");
            sqlBuffDef.append(" and defectHis.real_flg = panel.real_flg");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp <= '" + EvtEndTime + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and panel.panel_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.cald_date, defectHis.shf_key_fk, defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.lot_id,");
            sqlBuffDef.append(" defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, defectHis.pnl_grade, defectHis.ext_1, defectHis.defect_flg,");
            sqlBuffDef.append(" defectHis.operator_id, defectHis.rep_res,");
            sqlBuffDef.append(" defectHis.cel_repair_defect_code, defectHis.cel_repair_eng_defect_code, defectHis.cel_repair_jge_code");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date,");
            sqlInsrDef.append(" shf_key, shf_id, hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id,grade_id,");
            sqlInsrDef.append(" tft_glass_id, cf_glass_id, defect_flg, operator_id, rep_res, lcvd_rep_time, pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_defect_cnt,");
            sqlInsrDef.append(" cel_repair_defect_code, cel_repair_eng_defect_code, cel_repair_jge_code, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty)" + sqlBuffDef);
        } else if ("CF".equals(etlConfDModel.getShop())) {
            //获取源数据汇总defectqty
            sqlBuffDef.append(" select panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id, grade_id, tft_glass_id, cf_glass_id, defect_flg,");
            sqlBuffDef.append(" operator_id, rep_res, source_defect_jge, lcvd_rep_time, pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty from (");
            sqlBuffDef.append(" select defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, coalesce(defectHis.defect_code, '-') as defect_code, coalesce(defectHis.defect_size,'E') as defect_size, now() as db_timestamp, defectHis.cald_date,");
            sqlBuffDef.append(" defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner as owner_id,defectHis.pnl_grade as grade_id,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.panel_id, defectHis.ope_id, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.defect_flg, defectHis.rep_res, defectHis.source_defect_jge order by defectHis.evt_timestamp desc) as rowCnt,");
            sqlBuffDef.append(" defectHis.ext_1 as tft_glass_id, '' cf_glass_id, coalesce(defectHis.defect_flg,'-') as defect_flg, defectHis.operator_id, coalesce(defectHis.rep_res,'-') as rep_res, coalesce(defectHis.source_defect_jge,'-') as source_defect_jge, sum(defectHis.lcvd_rep_time) as lcvd_rep_time, panel.pnl_rej_type,");
            sqlBuffDef.append(" panel.pnl_judgment_grade, panel.pnl_jge, panel.pnl_defect_cnt, '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as defectqty");
//            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis");
            sqlBuffDef.append(" left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" on defectHis.panel_id = panel.panel_id");
            sqlBuffDef.append(" and defectHis.evt_timestamp = panel.evt_timestamp");
            sqlBuffDef.append(" and defectHis.ope_id = panel.ope_id");
            sqlBuffDef.append(" and defectHis.real_flg = panel.real_flg");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp <= '" + EvtEndTime + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and panel.panel_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.cald_date, defectHis.shf_key_fk, defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.lot_id,");
            sqlBuffDef.append(" defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, defectHis.pnl_grade, defectHis.ext_1, defectHis.defect_flg,");
            sqlBuffDef.append(" defectHis.operator_id, defectHis.rep_res, defectHis.source_defect_jge, panel.pnl_rej_type, panel.pnl_judgment_grade, panel.pnl_jge, panel.pnl_defect_cnt");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date,");
            sqlInsrDef.append(" shf_key, shf_id, hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id,grade_id,");
            sqlInsrDef.append(" tft_glass_id, cf_glass_id, defect_flg, operator_id, rep_res, source_defect_jge, lcvd_rep_time, pnl_rej_type,");
            sqlInsrDef.append(" pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty)" + sqlBuffDef);
        } else if ("A7700".equals(etlConfDModel.getOpe()) || "A7703".equals(etlConfDModel.getOpe()) || "A770X".equals(etlConfDModel.getOpe())
                || "A7710".equals(etlConfDModel.getOpe()) || "A7715".equals(etlConfDModel.getOpe()) || "A7750".equals(etlConfDModel.getOpe()) || "A7790".equals(etlConfDModel.getOpe())) {
            //获取源数据汇总defectqty
            sqlBuffDef.append(" select panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id, grade_id, tft_glass_id, cf_glass_id, defect_flg,");
            sqlBuffDef.append(" operator_id, rep_res, lcvd_rep_time, pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty from (");
            sqlBuffDef.append(" select defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, coalesce(defectHis.defect_code, '-') as defect_code, coalesce(defectHis.defect_size,'E') as defect_size, now() as db_timestamp, defectHis.cald_date,");
            sqlBuffDef.append(" defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner as owner_id,defectHis.pnl_grade as grade_id,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.panel_id, defectHis.ope_id, defectHis.defect_code, defectHis.defect_size, defectHis.operator_id, coalesce(defectHis.defect_zong,'-'), sum( defectHis.lcvd_rep_time ),");
            sqlBuffDef.append(" defectHis.defect_flg, defectHis.rep_res order by defectHis.evt_timestamp desc) as rowCnt,");
            sqlBuffDef.append(" defectHis.ext_1 as tft_glass_id, '' cf_glass_id, coalesce(defectHis.defect_flg,'-') as defect_flg, coalesce(defectHis.operator_id,'-') as operator_id, coalesce(defectHis.rep_res,'-') as rep_res, sum(defectHis.lcvd_rep_time) as lcvd_rep_time, panel.pnl_rej_type,");
            sqlBuffDef.append(" panel.pnl_judgment_grade, panel.pnl_jge, panel.pnl_defect_cnt, coalesce(defectHis.defect_zong,'-') as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as defectqty");
//            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis");
            sqlBuffDef.append(" left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" on defectHis.panel_id = panel.panel_id");
            sqlBuffDef.append(" and defectHis.evt_timestamp = panel.evt_timestamp");
            sqlBuffDef.append(" and defectHis.ope_id = panel.ope_id");
            sqlBuffDef.append(" and defectHis.real_flg = panel.real_flg");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp <= '" + EvtEndTime + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and panel.panel_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.cald_date, defectHis.shf_key_fk, defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.lot_id,");
            sqlBuffDef.append(" defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, defectHis.pnl_grade, defectHis.ext_1, defectHis.defect_flg,");
            sqlBuffDef.append(" defectHis.operator_id, defectHis.rep_res, panel.pnl_rej_type, panel.pnl_judgment_grade, panel.pnl_jge, panel.pnl_defect_cnt, defect_zong");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date,");
            sqlInsrDef.append(" shf_key, shf_id, hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id,grade_id,");
            sqlInsrDef.append(" tft_glass_id, cf_glass_id, defect_flg, operator_id, rep_res, lcvd_rep_time, pnl_rej_type,");
            sqlInsrDef.append(" pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty)" + sqlBuffDef);
        } else if ("ARRAY".equals(etlConfDModel.getShop())){
            //获取源数据汇总defectqty
            sqlBuffDef.append(" select panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id, grade_id, tft_glass_id, cf_glass_id, defect_flg,");
            sqlBuffDef.append(" operator_id, rep_res, lcvd_rep_time, pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty from (");
            sqlBuffDef.append(" select defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, coalesce(defectHis.defect_code, '-') as defect_code, coalesce(defectHis.defect_size,'E') as defect_size, now() as db_timestamp, defectHis.cald_date,");
            sqlBuffDef.append(" defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner as owner_id,defectHis.pnl_grade as grade_id,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.panel_id, defectHis.ope_id, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.defect_flg, defectHis.rep_res, coalesce(defectHis.defect_zong,'-') order by defectHis.evt_timestamp desc) as rowCnt,");
            sqlBuffDef.append(" defectHis.ext_1 as tft_glass_id, '' cf_glass_id, coalesce(defectHis.defect_flg,'-') as defect_flg, defectHis.operator_id, coalesce(defectHis.rep_res,'-') as rep_res, sum(defectHis.lcvd_rep_time) as lcvd_rep_time, panel.pnl_rej_type,");
            sqlBuffDef.append(" panel.pnl_judgment_grade, panel.pnl_jge, panel.pnl_defect_cnt, coalesce(defectHis.defect_zong,'-') as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as defectqty");
//            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis");
            sqlBuffDef.append(" left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" on defectHis.panel_id = panel.panel_id");
            sqlBuffDef.append(" and defectHis.evt_timestamp = panel.evt_timestamp");
            sqlBuffDef.append(" and defectHis.ope_id = panel.ope_id");
            sqlBuffDef.append(" and defectHis.real_flg = panel.real_flg");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp <= '" + EvtEndTime + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and panel.panel_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.cald_date, defectHis.shf_key_fk, defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.lot_id,");
            sqlBuffDef.append(" defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, defectHis.pnl_grade, defectHis.ext_1, defectHis.defect_flg,");
            sqlBuffDef.append(" defectHis.operator_id, defectHis.rep_res, panel.pnl_rej_type, panel.pnl_judgment_grade, panel.pnl_jge, panel.pnl_defect_cnt, defect_zong");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date,");
            sqlInsrDef.append(" shf_key, shf_id, hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id,grade_id,");
            sqlInsrDef.append(" tft_glass_id, cf_glass_id, defect_flg, operator_id, rep_res, lcvd_rep_time, pnl_rej_type,");
            sqlInsrDef.append(" pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty)" + sqlBuffDef);
        } else {
            //获取源数据汇总defectqty
            sqlBuffDef.append(" select panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id, grade_id, tft_glass_id, cf_glass_id, defect_flg,");
            sqlBuffDef.append(" operator_id, rep_res, lcvd_rep_time, pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty from (");
            sqlBuffDef.append(" select defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, coalesce(defectHis.defect_code, '-') as defect_code, coalesce(defectHis.defect_size,'E') as defect_size, now() as db_timestamp, defectHis.cald_date,");
            sqlBuffDef.append(" defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner as owner_id,defectHis.pnl_grade as grade_id,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.panel_id, defectHis.ope_id, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.defect_flg, defectHis.rep_res order by defectHis.evt_timestamp desc) as rowCnt,");
            sqlBuffDef.append(" defectHis.ext_1 as tft_glass_id, '' cf_glass_id, coalesce(defectHis.defect_flg,'-') as defect_flg, defectHis.operator_id, coalesce(defectHis.rep_res,'-') as rep_res, sum(defectHis.lcvd_rep_time) as lcvd_rep_time, panel.pnl_rej_type,");
            sqlBuffDef.append(" panel.pnl_judgment_grade, panel.pnl_jge, panel.pnl_defect_cnt, '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as defectqty");
//            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis");
            sqlBuffDef.append(" left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" on defectHis.panel_id = panel.panel_id");
            sqlBuffDef.append(" and defectHis.evt_timestamp = panel.evt_timestamp");
            sqlBuffDef.append(" and defectHis.ope_id = panel.ope_id");
            sqlBuffDef.append(" and defectHis.real_flg = panel.real_flg");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
                sqlBuffDef.append(" and panel.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and panel.evt_timestamp <= '" + EvtEndTime + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and panel.panel_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.panel_id, defectHis.ope_id, defectHis.evt_timestamp, defectHis.defect_code, defectHis.defect_size,");
            sqlBuffDef.append(" defectHis.cald_date, defectHis.shf_key_fk, defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.lot_id,");
            sqlBuffDef.append(" defectHis.glass_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, defectHis.pnl_grade, defectHis.ext_1, defectHis.defect_flg,");
            sqlBuffDef.append(" defectHis.operator_id, defectHis.rep_res, panel.pnl_rej_type, panel.pnl_judgment_grade, panel.pnl_jge, panel.pnl_defect_cnt");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (panel_id, ope_id, evt_timestamp, defect_code, defect_size, db_timestamp, cald_date,");
            sqlInsrDef.append(" shf_key, shf_id, hour_key, shop_id, lot_id, glass_id, eqpt_id, prod_id, owner_id,grade_id,");
            sqlInsrDef.append(" tft_glass_id, cf_glass_id, defect_flg, operator_id, rep_res, lcvd_rep_time, pnl_rej_type,");
            sqlInsrDef.append(" pnl_judgment_grade, pnl_jge, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5, defectqty)" + sqlBuffDef);
        }

        try {
            SQLQuery InsrDef_query = session.createSQLQuery(sqlInsrDef.toString());
            logger.info("Insert data SQL:" + sqlInsrDef.toString());

            Date startTime = new Date();

            InsrDef_query.executeUpdate();

            Date endTime = new Date();
            long durableTime = (endTime.getTime() - startTime.getTime())/1000;

            logger.info(defectShop + " "+  tmpTab +" insert data from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
            logger.info("Insert data耗时:" + durableTime +"s");

            okCnt++;
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlInsrDef.toString():  **********    " + sqlInsrDef.toString());

            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }
            throw new RuntimeException(e);
        }

        //Insert之后是否成功都将redis存储的对应的数据删除，后面对比数据之后再进行插入
        if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
            final JedisPool jedisPool = SpringContext.getBean(JedisPool.class);
            try (Jedis jedis = jedisPool.getResource()) {
                jedis.hdel("reload_panel", etlConfDModel.getOpe() + etlConfDModel.getPanelList());
            } catch (Exception e) {
                logger.error(LoggerUtil.stackTraceToString(e));
                logger.error("************   Redis Insert Error  **********    " + etlConfDModel.getOpe() + etlConfDModel.getPanelList());
            }
        }
    }


    public void insertRedis(EtlConfDModel etlConfDModel, String EvtStartTime, String EvtEndTime, String tmpTab, Boolean loopFlag) {
        List<DReloadPnlGls> pnlGlsList = new ArrayList<>();
        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();

        String sqlBuffIns = "";
        Boolean isGlass = false;

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
        }

        //一些站点数据只存glass表
        if (opeList.contains(etlConfDModel.getOpe())) {
            isGlass = true;
            //获取源数据汇总defectqty
            sqlBuffIns = " select glass_id from (" +
                    " select defectHis.evt_timestamp,defectHis.ope_id,defectHis.panel_id,panel.pnl_defect_cnt,count(defectHis.defect_code) defect_cnt" +
                    " from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis" +
                    " left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_glass_f panel" +
                    " on defectHis.panel_id = panel.glass_id" +
                    " and defectHis.evt_timestamp = panel.evt_timestamp" +
                    " and defectHis.ope_id = panel.ope_id" +
                    " and defectHis.real_flg = panel.real_flg";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'" +
                        " and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";

                EvtStartTime = etlConfDModel.getRunStartTimestamp().toString();
                EvtEndTime = etlConfDModel.getRunEndTimestamp().toString();
            } else if (loopFlag == true){
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffIns += " and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and defectHis.real_flg = 'Y'";

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffIns += " and panel.glass_id in ('"+ panelList + "')";
            }

            sqlBuffIns += " group by defectHis.evt_timestamp,defectHis.ope_id,defectHis.glass_id,panel.pnl_defect_cnt" +
                    " ) pnlDefect" +
                    " where pnl_defect_cnt <> defect_cnt";
        } else if ("466X".equals(etlConfDModel.getOpe()) || "6641".equals(etlConfDModel.getOpe())) {
            isGlass = true;
            //获取源数据汇总defectqty
            sqlBuffIns = " select glass_id from (" +
                    " select defectHis.evt_timestamp,defectHis.ope_id,defectHis.panel_id,panel.pnl_defect_cnt,count(defectHis.defect_code) defect_cnt" +
                    " from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis" +
                    " left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_glass_f panel" +
                    " on defectHis.panel_id = panel.glass_id" +
                    " and defectHis.evt_timestamp = panel.evt_timestamp" +
                    " and defectHis.ope_id = panel.ope_id" +
                    " and defectHis.real_flg = panel.real_flg";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'" +
                        " and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";

                EvtStartTime = etlConfDModel.getRunStartTimestamp().toString();
                EvtEndTime = etlConfDModel.getRunEndTimestamp().toString();
            } else if (loopFlag == true){
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffIns += " and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and defectHis.real_flg = 'Y'";

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffIns += " and panel.glass_id in ('"+ panelList + "')";
            }

            sqlBuffIns += " group by defectHis.evt_timestamp,defectHis.ope_id,defectHis.glass_id,panel.pnl_defect_cnt" +
                    " ) pnlDefect" +
                    " where pnl_defect_cnt <> defect_cnt";
        } else if ("CF".equals(etlConfDModel.getShop())) {
            isGlass = false;
            //获取源数据汇总defectqty
            sqlBuffIns = " select panel_id from (" +
                    " select defectHis.evt_timestamp,defectHis.ope_id,defectHis.panel_id,panel.pnl_defect_cnt,count(defectHis.defect_code) defect_cnt" +
                    " from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis" +
                    " left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel" +
                    " on defectHis.panel_id = panel.glass_id" +
                    " and defectHis.evt_timestamp = panel.evt_timestamp" +
                    " and defectHis.ope_id = panel.ope_id" +
                    " and defectHis.real_flg = panel.real_flg";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'" +
                        " and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";

                EvtStartTime = etlConfDModel.getRunStartTimestamp().toString();
                EvtEndTime = etlConfDModel.getRunEndTimestamp().toString();
            } else if (loopFlag == true){
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffIns += " and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and defectHis.real_flg = 'Y'";

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffIns += " and panel.panel_id in ('"+ panelList + "')";
            }

            sqlBuffIns += " group by defectHis.evt_timestamp,defectHis.ope_id,defectHis.panel_id,panel.pnl_defect_cnt" +
                    " ) pnlDefect" +
                    " where pnl_defect_cnt <> defect_cnt";
        } else if ("A7700".equals(etlConfDModel.getOpe()) || "A7703".equals(etlConfDModel.getOpe()) || "A770X".equals(etlConfDModel.getOpe())
                || "A7710".equals(etlConfDModel.getOpe()) || "A7715".equals(etlConfDModel.getOpe()) || "A7750".equals(etlConfDModel.getOpe()) || "A7790".equals(etlConfDModel.getOpe())) {
            isGlass = false;
            //获取源数据汇总defectqty
            sqlBuffIns = " select panel_id from (" +
                    " select defectHis.evt_timestamp,defectHis.ope_id,defectHis.panel_id,panel.pnl_defect_cnt,count(defectHis.defect_code) defect_cnt" +
                    " from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis" +
                    " left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel" +
                    " on defectHis.panel_id = panel.glass_id" +
                    " and defectHis.evt_timestamp = panel.evt_timestamp" +
                    " and defectHis.ope_id = panel.ope_id" +
                    " and defectHis.real_flg = panel.real_flg";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'" +
                        " and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";

                EvtStartTime = etlConfDModel.getRunStartTimestamp().toString();
                EvtEndTime = etlConfDModel.getRunEndTimestamp().toString();
            } else if (loopFlag == true){
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffIns += " and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and defectHis.real_flg = 'Y'";

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffIns += " and panel.panel_id in ('"+ panelList + "')";
            }

            sqlBuffIns += " group by defectHis.evt_timestamp,defectHis.ope_id,defectHis.panel_id,panel.pnl_defect_cnt" +
                    " ) pnlDefect" +
                    " where pnl_defect_cnt <> defect_cnt";
        } else {
            isGlass = false;
            //获取源数据汇总defectqty
            sqlBuffIns = " select panel_id from (" +
                    " select defectHis.evt_timestamp,defectHis.ope_id,defectHis.panel_id,panel.pnl_defect_cnt,count(defectHis.defect_code) defect_cnt" +
                    " from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f_n defectHis" +
                    " left join wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel" +
                    " on defectHis.panel_id = panel.glass_id" +
                    " and defectHis.evt_timestamp = panel.evt_timestamp" +
                    " and defectHis.ope_id = panel.ope_id" +
                    " and defectHis.real_flg = panel.real_flg";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'" +
                        " and panel.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";

                EvtStartTime = etlConfDModel.getRunStartTimestamp().toString();
                EvtEndTime = etlConfDModel.getRunEndTimestamp().toString();
            } else if (loopFlag == true){
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp < '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sqlBuffIns += " where defectHis.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and defectHis.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.evt_timestamp >= '" + EvtStartTime + "'" +
                        " and panel.evt_timestamp <= '" + EvtEndTime + "'" +
                        " and panel.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and panel.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffIns += " and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and defectHis.real_flg = 'Y'";

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffIns += " and panel.panel_id in ('"+ panelList + "')";
            }

            sqlBuffIns += " group by defectHis.evt_timestamp,defectHis.ope_id,defectHis.panel_id,panel.pnl_defect_cnt" +
                    " ) pnlDefect" +
                    " where pnl_defect_cnt <> defect_cnt";
        }

        try {
            pnlGlsList = qr_gp.query(sqlBuffIns, new BeanListHandler<DReloadPnlGls>(DReloadPnlGls.class));
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR get etlHisJob data!" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }
        }

        if (pnlGlsList != null && pnlGlsList.size() > 0) {
            ReloadPanel reloadPanel = new ReloadPanel();
            reloadPanel.setStart_evt_timestamp(EvtStartTime);
            reloadPanel.setEnd_evt_timestamp(EvtEndTime);
            reloadPanel.setStart_db_timestamp(etlConfDModel.getRunStartTimestamp().toString());
            reloadPanel.setEnd_db_timestamp(etlConfDModel.getRunEndTimestamp().toString());
            reloadPanel.setOpe_id(etlConfDModel.getOpe());
            BeanUtils.copyProperties(pnlGlsList, reloadPanel.getPanelList());
            final Boolean isGlassFlag = isGlass;
            final JedisPool jedisPool = SpringContext.getBean(JedisPool.class);
            try (Jedis jedis = jedisPool.getResource()) {
                if (isGlassFlag) {
                    jedis.hset("reload_panel", reloadPanel.getOpe_id() + reloadPanel.getPanelList().get(0), JacksonUtil.toJSONStr(reloadPanel));
                }
            }
        }
    }

    //查寻etl_conf_d表的his job当中的ext_2值
    public List<EtlConfDDefect> getHisJobFlag(EtlConfD etlConfD) {
        List<EtlConfDDefect> etlConfDList = new ArrayList<EtlConfDDefect>();
        QueryRunner qr_mysql = JdbcUtil.getQueryRunner_mysql();

        String jobName = etlConfD.getJobName();
        String hisJobFlagSql = "select ext_2 from ch_qms.etl_conf_d" +
                " where job_group_name = 'RPT_ETL'" +
                " and job_name = '" + jobName.substring(0, jobName.length() - 2) + "_His" + jobName.substring(jobName.length() - 2) + "'";

        try {
            etlConfDList = qr_mysql.query(hisJobFlagSql, new BeanListHandler<EtlConfDDefect>(EtlConfDDefect.class));  //获取etl_conf_d表中his job的ext_2，作为current job的run job的参考
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR get etlHisJob data!" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }

        }

        return etlConfDList;
    }

    //查寻etl_conf_d表的current job当中的ext_2值
    public List<EtlConfDDefect> getCurJob(EtlConfD etlConfD) {
        List<EtlConfDDefect> etlConfDList = new ArrayList<EtlConfDDefect>();
        QueryRunner qr_mysql = JdbcUtil.getQueryRunner_mysql();

        String jobName = etlConfD.getJobName();
        String hisJobFlagSql = "select RUN_END_TIMESTAMP,BUFFER_MINUTE,QUERY_MINUTE,EXT_2 from ch_qms.etl_conf_d" +
                " where job_group_name = 'RPT_ETL'" +
                " and job_name = '" + jobName.substring(0, jobName.length() - 6) + jobName.substring(jobName.length() - 2) + "'";

        try {
            etlConfDList = qr_mysql.query(hisJobFlagSql, new BeanListHandler<EtlConfDDefect>(EtlConfDDefect.class));  //获取etl_conf_d表中his job的ext_2，作为current job的run job的参考
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR get etlHisJob data!" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }

        }

        return etlConfDList;
    }

    @Transactional(transactionManager = "primaryPlatformTransactionManager")
    public void updateEtlConfD(EtlConfD etlConfD) {
        dao.update(etlConfD);
    }


    @Transactional(transactionManager = "primaryPlatformTransactionManager")
    public void updateEtlConfDModel(EtlConfDModel etlConfDModel) {
        dao.update(etlConfDModel);
    }

    //获取时间段内panel的最小时间，作为delete defectSummary表中的起始时间
    public List<YearMonth> getYearMonth(EtlConfDModel etlConfDModel, String tmpTab) {
        List<YearMonth> yearMonthList = new ArrayList<YearMonth>();
        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();

        String sqlBuffDef = "";

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("466X");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("6641");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
        }

        if (opeList.contains(etlConfDModel.getOpe())) {
            //获取源数据汇总defectqty
            sqlBuffDef = "select glass_id, min(substring(glass_id,2,2)) as year, min(substring(glass_id,4,1)) as month" +
                    "  from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_glass_f";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffDef = sqlBuffDef + " where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sqlBuffDef = sqlBuffDef + " where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffDef = sqlBuffDef + " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and real_flg = 'Y'" +
                    " and substring(glass_id,1,1) in ('A','C')" +
                    " and glass_id not like '%*%'" +
                    " group by glass_id" +
                    " order by year, month";
        } else {
            //获取源数据汇总defectqty
            sqlBuffDef = "select glass_id, min(substring(glass_id,2,2)) as year, min(substring(glass_id,4,1)) as month" +
                    "  from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sqlBuffDef = sqlBuffDef + " where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sqlBuffDef = sqlBuffDef + " where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffDef = sqlBuffDef + " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and real_flg = 'Y'" +
                    " and substring(glass_id,1,1) in ('A','C')" +
                    " and glass_id not like '%*%'" +
                    " group by glass_id " +
                    " order by year, month";
        }

        try {
            yearMonthList = qr_gp.query(sqlBuffDef, new BeanListHandler<YearMonth>(YearMonth.class));  //获取panel的最小时间
            logger.info("get panel min glass_id time " + "from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR hisAndExtToTmp GET COLUMN_NAME:" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }
        }

        return yearMonthList;
    }

    public List<MinMaxEvttimeStamp> getMinMaxEvtTime(EtlConfDModel etlConfDModel, String tmpTab) {
        List<MinMaxEvttimeStamp> minMaxEvtTime = new ArrayList<MinMaxEvttimeStamp>();
        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();

        String sql = "";

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("466X");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("6641");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
        }

        if (opeList.contains(etlConfDModel.getOpe())) {
            sql = "select max( evt_timestamp ) maxTime, min( evt_timestamp ) minTime" +
                    " from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_glass_f";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sql = sql + " where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sql = sql + " where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sql = sql + " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and real_flg = 'Y'";
        } else {
            sql = "select max( evt_timestamp ) maxTime, min( evt_timestamp ) minTime" +
                    " from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if (etlConfDModel.getExt1().toString().equals("Y")) {
                sql = sql + " where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
//                SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//                Date startDate = new Date();
//                Date endDate = new Date();
//                try {
//                    startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                    endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//                } catch (ParseException e) {
//                    e.printStackTrace();
//                }
//                Calendar calStart = Calendar.getInstance();
//                calStart.setTime(startDate);
//                calStart.add(Calendar.MONTH, -1);
//
//                Calendar calEnd = Calendar.getInstance();
//                calEnd.setTime(endDate);
//                calEnd.add(Calendar.DATE, 10);

                sql = sql + " where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sql = sql + " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and real_flg = 'Y'";
        }

        try {
            minMaxEvtTime = qr_gp.query(sql, new BeanListHandler<MinMaxEvttimeStamp>(MinMaxEvttimeStamp.class));  //获取panel的最小时间
            logger.info("get panel min evt_timestamp " + "from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR hisAndExtToTmp GET COLUMN_NAME:" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }
        }

        return minMaxEvtTime;
    }
}
